Oracle Database 11g: Analytic SQL for Data Warehousing Content Details

Introduction

• Course Objectives
• Course Agenda
• Class Accounts Information
• Appendices Used in this Course
• Sample Schemas Used in this Course
• SQL Environments Available in the Course
• Overview of Oracle SQL Developer
• Oracle 11g SQL and Data Warehousing Documentation and Additional Resources

Grouping and Aggregating Data Using SQL

• What is Analytic SQL?
• Analytic SQL in Data Warehouses Agenda: SQL for Aggregation
• SQL for Analysis and Reporting, and SQL for Modeling
• Generating Reports by Grouping Related Data
• Using the GROUP BY Clause With the ROLLUP and CUBE Operators
• Using the ROLLUP and CUBE Operators
• Using the GROUPING Function
• Working With GROUPING SETS
• Working With Composite Columns and Concatenated Groupings

Analyzing and Reporting Data Using SQL

• Overview of SQL for Analysis and Reporting Functions
• Identifying the SQL Ranking Functions
• Controlling the Ranking Order
• Ranking on Multiple Expressions
• Using the RANK, DENSE_RANK, and PERCENT_RANK Functions
• Ranking Per CUBE and ROLLUP
• Using the LAG/LEAD Functions
• Performing Pivoting Operations Using the PIVOT and UNPIVOT Clauses

Modeling Data Using SQL

• Overview of SQL for Modeling Data
• Integrating Inter-row Calculations in SQL
• Working With the SQL MODEL Clause
• Cell and Range References
• Using the CV()Function
• Using the FOR Construct with IN List Operator, Incremental Values, and a Sub query
• Using Reference Models
• Cyclic Rules in Models

Hierarchical Retrieval

• Hierarchical Retrieval: Overview
• Natural Tree Structure
• Hierarchical Queries
• Walking the Tree
• Walking the Tree: From the Bottom Up and From the Top Down
• Ranking Rows with the LEVEL Pseudo column
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches and Nodes

Analyzing Data Using Regular Expressions

• The Benefits of Using Regular Expressions
• Using the Regular Expressions Functions and Conditions in SQL
• Using Met a characters with Regular Expressions
• Performing a Basic Search Using the REGEXP_LIKE Condition
• Finding Patterns Using the REGEXP_INSTR Function
• Extracting Substrings Using the REGEXP_SUBSTR Function
• Replacing Patterns Using the REGEXP_REPLACE Function
• Using Sub expressions with Regular Expression Support